﻿namespace DBUtility
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.OleDb;
    using System.IO;
    using System.Reflection;
    using System.Runtime.InteropServices;

    public class DbHelperOledb
    {
        private static Hashtable dbAddress = new Hashtable();
        private static string dbAddressTextPath = (Directory.GetParent(Assembly.GetExecutingAssembly().Location) + @"\1");

        public static DataTable ExecutePager(string dbname, int pageIndex, int pageSize, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount, string idname)
        {
            if (orderString == "")
            {
                orderString = idname + " desc";
            }
            if (orderString == "$%")
            {
                orderString = "";
            }
            using (OleDbConnection connection = new OleDbConnection(GetDbConnString(dbname)))
            {
                OleDbCommand command2;
                connection.Open();
                if (pageIndex < 1)
                {
                    pageIndex = 1;
                }
                if (pageSize < 1)
                {
                    pageSize = 10;
                }
                if (string.IsNullOrEmpty(showString))
                {
                    showString = "*";
                }
                if (string.IsNullOrEmpty(orderString))
                {
                    orderString = "";
                }
                string str2 = string.Format(" ( {0} ) tempVw ", queryString);
                recordCount = Convert.ToInt32(new OleDbCommand(string.Format(" select count(0) as recordCount from {0} {1}", str2, whereString), connection).ExecuteScalar());
                if ((recordCount % pageSize) > 0)
                {
                    pageCount = (recordCount / pageSize) + 1;
                }
                else
                {
                    pageCount = recordCount / pageSize;
                }
                if (pageIndex == 1)
                {
                    if (orderString != "")
                    {
                        command2 = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", new object[] { pageSize, showString, str2, whereString, orderString }), connection);
                    }
                    else
                    {
                        command2 = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} ", new object[] { pageSize, showString, str2, whereString }), connection);
                    }
                }
                else if (pageIndex > pageCount)
                {
                    if (orderString != "")
                    {
                        command2 = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", new object[] { pageSize, showString, str2, "where 1=2", orderString }), connection);
                    }
                    else
                    {
                        command2 = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} ", new object[] { pageSize, showString, str2, "where 1=2" }), connection);
                    }
                }
                else
                {
                    string str3;
                    int num = pageSize * pageIndex;
                    int passCount = num - pageSize;
                    if (orderString != "")
                    {
                        str3 = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", new object[] { num, idname, str2, whereString, orderString }), passCount, connection);
                        command2 = new OleDbCommand(string.Format("select {0} from {1} where " + idname + " in ({2}) order by {3} ", new object[] { showString, str2, str3, orderString }), connection);
                    }
                    else
                    {
                        str3 = recordID(string.Format("select top {0} {1} from {2} {3} ", new object[] { num, idname, str2, whereString }), passCount, connection);
                        command2 = new OleDbCommand(string.Format("select {0} from {1} where " + idname + " in ({2})", showString, str2, str3), connection);
                    }
                }
                OleDbDataAdapter adapter = new OleDbDataAdapter(command2);
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                connection.Close();
                return dataTable;
            }
        }

        public static int ExecuteSql(string dbname, string SQLString)
        {
            int num2;
            using (OleDbConnection connection = new OleDbConnection(GetDbConnString(dbname)))
            {
                OleDbCommand command = new OleDbCommand(SQLString, connection);
                try
                {
                    connection.Open();
                    return command.ExecuteNonQuery();
                }
                catch (OleDbException exception)
                {
                    connection.Close();
                    throw new Exception(exception.Message);
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return num2;
        }

        public static string GetDbConnString(string dbname)
        {
            string str2;
            try
            {
                if (dbAddress.Count <= 0)
                {
                    StreamReader reader = new StreamReader(dbAddressTextPath);
                    string str = "";
                    while ((str = reader.ReadLine()) != null)
                    {
                        if (str.Trim() != "")
                        {
                            string[] strArray = str.Split(new char[] { '|' });
                            if (strArray.Length > 0)
                            {
                                dbAddress.Add(strArray[0].ToLower(), strArray[1]);
                            }
                        }
                    }
                    return string.Concat(new object[] { "Jet OleDb:DataBase Password='weiweibtm';Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", Directory.GetParent(Assembly.GetExecutingAssembly().Location), @"\", dbAddress[dbname.ToLower()].ToString() });
                }
                str2 = string.Concat(new object[] { "Jet OleDb:DataBase Password='weiweibtm';Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", Directory.GetParent(Assembly.GetExecutingAssembly().Location), @"\", dbAddress[dbname.ToLower()].ToString() });
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            return str2;
        }

        public static int GetMaxID(string dbname, string FieldName, string TableName, string sWhere)
        {
            string dbConnString = GetDbConnString(dbname);
            string sQLString = "";
            if (sWhere != "")
            {
                sQLString = "select max(" + FieldName + ") from " + TableName + " where " + sWhere;
            }
            else
            {
                sQLString = "select max(" + FieldName + ") from " + TableName;
            }
            DataTable table = Query(dbname, sQLString);
            if (table == null)
            {
                return 1;
            }
            return int.Parse(table.Rows[0][0].ToString());
        }

        public static DataTable Query(string dbname, string SQLString)
        {
            DataTable table;
            using (OleDbConnection connection = new OleDbConnection(GetDbConnString(dbname)))
            {
                OleDbCommand command = new OleDbCommand(SQLString, connection);
                try
                {
                    connection.Open();
                    DataSet dataSet = new DataSet();
                    new OleDbDataAdapter(SQLString, connection).Fill(dataSet, "ds");
                    return dataSet.Tables[0];
                }
                catch (OleDbException exception)
                {
                    connection.Close();
                    throw new Exception(exception.Message);
                }
                finally
                {
                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
            return table;
        }

        private static string recordID(string query, int passCount, OleDbConnection conn)
        {
            OleDbCommand command = new OleDbCommand(query, conn);
            string str = string.Empty;
            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (passCount < 1)
                    {
                        str = str + "," + reader[0];
                    }
                    passCount--;
                }
            }
            return str.Remove(0, 1);
        }
    }
}

